Materialized View Selection for Multidimensional Datasets

To fulfill the requirement of fast interactive multidimensional data analysis, database systems precompute aggregate views on some subsets of dimensions and their corresponding hierarchies. However, the problem of what to precompute is difficult and intriguing. The leading existing algorithm, BPUS, has a running time that is polynomial in the number of views and is guaranteed to be within (0.63 f) of optimal, where f is the fraction of available space consumed by the largest aggregate. Unfortunately, BPUS can be impractically slow, and in some instances may miss good solutions due to the coarse granularity at which it makes its decisions of what to precompute. In view of this, we study the structure of the precomputation problem and show that under certain broad conditions on the multidimensional data, an even simpler and faster algorithm, PBS, achieves the same (0.63 f) bound. Our empirical study of the behavior of PBS shows that even when this condition does not hold, PBS picks a surprisingly good set of aggregates for precomputation. Furthermore, BPUS and other previous work has assumed that all aggregates are either precomputed in their entirety or not at all. We show that if one relaxes this and allows aggregates to be partially precomputed, not only is it possible to find solutions that are better than those found by previous algorithms, in some cases it is even possible to find solutions that are better than the solution that is ‘optimal’ by the previous definition. This research is supported by a gift from NCR Corp., and by ARPA through Rome Air Force Laboratory contract F30602-972-0247 Permission to copy without fee all or part of this material is gmnted provided that the copies are not made or distributed for direct commercial advantage, the VLDB copyright notice and the title of the publication and its date appear, and notice is given that copying is by permission of the Very Large Data Base Endowment. To copy otherwise, or to republish, requires a fee and/or special permission from the Endowment. Proceedings of the 24th VLDB Conference New York, USA, 1998 Jeffrey F. Naughton [email protected]